

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;

import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * Servlet implementation class Client_zvit
 */
public class Client_zvit extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Client_zvit() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		request.setCharacterEncoding("UTF-8");
		HttpSession Session=request.getSession();
		String atr=(String)request.getParameter("Begin");
		PrintWriter out= response.getWriter();
		ResultSet rs = null;
		Statement st = null;
		Connection cn = null;
		DataSource ds = null;
		if (atr==null){ 
			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title></head><body>");
			out.println("<form method=\"get\">Начало периода:<input name=\"Begin\"><br>Конец периода:<input name=\"End\"><br>Клиент:<select name=\"name\">");
			try {
				InitialContext initCtx = new InitialContext();
				ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
				cn=ds.getConnection();
				st=cn.createStatement();
				rs=st.executeQuery("select id_client,trim(name) from client order by UPPER(trim(name))");
				while (rs.next()){
					out.println(String.format("<option value=\"%s\">%s</option>", rs.getString(1), rs.getString(2)));
				}

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			out.println("</select><br><input type=\"submit\" name=\"BOk\" value=\"     Ok!      \"></form></body></html>");
			try {
				rs.close();
				st.close();
				cn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		else{
			setBegin(request.getParameter("Begin"), Session);
			setEnd(request.getParameter("End"), Session);
			setClient(request.getParameter("name"), Session);
			response.setContentType("text/html");
			response.setCharacterEncoding("UTF-8");
			String SQL=String.format("select trim(dt.name)||' №'||d.numb||' от '||to_char(d.day,'DD.MM.YYYY')||', склад: '||trim(s.name) as text, dt.operacia, d.sum*c.curs as sum, nvl(trim(substr(note,2)),'-') as note, d.day"+ 
					" from DOCUMENT d, TYPE_DOC dt, SKLAD s, CURS c where d.day>to_date('%1$s','DD/MM/YYYY')" +
					" and d.day<to_date('%2$s','DD/MM/YYYY') and d.id_type_doc=dt.id_type_doc and d.id_client=%3$s and d.id_skl=s.id_skl and c.id_val=d.id_val and c.day=(select max(day) from CURS c where c.day<d.day and id_val=d.id_val)"+ 
					" union"+
					" select trim(dt.name)||' №'||d.numb||' от '||to_char(d.day,'DD.MM.YYYY') as text, dt.operacia, d.sum*c.curs as sum, nvl(trim(substr(note,2)),'-') as note, d.day from fin_doc d, TYPE_DOC dt, curs c where d.day>to_date('%1$s','DD/MM/YYYY')"+ 
					" and d.day<to_date('%2$s','DD/MM/YYYY') and d.id_type_doc=dt.id_type_doc and d.id_client=%3$s and c.id_val=d.id_val and c.day=(select max(day) from CURS c where c.day<d.day and id_val=d.id_val)"+ 
					" order by day", getBegin(Session),getEnd(Session),getClient(Session));
			try {
				InitialContext initCtx = new InitialContext();
				ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
				cn=ds.getConnection();
				st=cn.createStatement();
				rs=st.executeQuery(SQL);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title></head><body>");
			out.println(String.format("<p align=\"center\">Отчет по %s <br> за период с %s по %s </p>",getClientName(Session),getBegin(Session),getEnd(Session)) );
			out.println("<table border=\"1\" align=\"center\" cellpadding=\"5\"><thead><tr align=\"center\"><th>Документ</th><th>Приход <br> грн. </th><th>Расход<br> грн. </th><th> Примечание </th></tr></thead>");
			try {
				while (rs.next())
					out.println(String.format("<tr><td>%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td>%s</td></tr>", rs.getString(1), 
							(rs.getInt(2)==1||rs.getInt(2)==3?((new DecimalFormat("0.00")).format(rs.getDouble(3))).replace('.', ','):"-"), (rs.getInt(2)==2||rs.getInt(2)==4?((new DecimalFormat("0.00")).format(rs.getDouble(3))).replace('.', ','):"-"),rs.getString(4)));
			}catch(SQLException e){
				e.printStackTrace();
			}
			out.println("</table></body></html>");
			try {
				rs.close();
				st.close();
				cn.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	private void setBegin(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("begin", Value);
		}
	}
	private void setEnd(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("end", Value);
		}
	}
	private void setClient(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("sklad", Value);
		}
	}
	
	private String getBegin(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("begin");
		else
			return "";
	}
	private String getEnd(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("end");
		else
			return "";
	}
	private String getClient(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("sklad");
		else
			return "";
	}
	private String getClientName(HttpSession session){
		ResultSet rs=null;
		String ret="";
		try {
			InitialContext initCtx = new InitialContext();
			DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
			Connection cn=ds.getConnection();
			Statement st=cn.createStatement();
			String SQL=String.format("select trim(name) from client where id_client=%s",getClient(session));
			rs=st.executeQuery(SQL);
			if (rs.next()){
				ret=rs.getString(1);
			}
			rs.close();
			st.close();
			cn.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}


}
